#include "dbmanager.h"
//结果集头文件
#include <QSqlRecord>
//饿汉式
DbManager* DbManager::dbPter = new DbManager;

DbManager::DbManager()
{

    //1.连接数据库驱动（当前程序 相连接的是哪个数据库）
    db = QSqlDatabase::addDatabase("QSQLITE");
    //2.创建 数据库文件 stu.db
    //相对路径：build文件夹 （项目--》构建目录）
    //绝对路径： E: /test/stu.db
    db.setDatabaseName("music.db");
    //3.打开数据库
    db.open();
    //4.创建学生表
    createTable();

}

DbManager::DbManager(const DbManager &)
{

}

void DbManager::createTable()
{
    QString sql = "create table if not exists history (id integer primary key autoincrement,"
                  "userName varchar(20),musicName varchar(50),updateTime datetime);";
    //创建执行sql语句的对象
    QSqlQuery query;
    //带着sql语句 去数据库执行
    bool ok = query.exec(sql);
    if(ok)
        qDebug()<<"创建成功";
    else
        qDebug()<<"fail";


    sql = "create table if not exists user (id integer primary key autoincrement,"
          "userName varchar(20),passWord varchar(20));";

    ok = query.exec(sql);
    if(ok)
        qDebug()<<"创建用户表成功";
    else
        qDebug()<<"fail";
}

DbManager *DbManager::getDbPtr()
{
    return dbPter;
}

bool DbManager::insertHistory(SongInfo info)
{
    //插入历史记录
    QString sql = "insert into history(userName,musicName,updateTime) values(?,?,datetime('now','localtime'));";
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,info.userName);
    query.bindValue(1,info.musicName);
    bool ok = query.exec();
    if(ok)
        qDebug()<<"插入成功";
    else
        qDebug()<<"插入失败";
    return ok;
}

void DbManager::selectHistory(vector<SongInfo>& vec,QString name)
{
    QString sql = "select * from history where username = ?";
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,name);
    query.exec();

    //2.从结果集中取数据，界面的显示
    while(query.next())//箭头所值 的行 有 有效数据，next函数的返回值为真
    {
        //提取一行数据的逻辑 id name score
        //record 数据集 0:第一个字段
        //QVariant 泛型
        QString  musicName = query.record().value(2).toString();
        QDateTime time = query.record().value(3).toDateTime();
        SongInfo info;
        info.musicName = musicName;
        info.updateTime = time;
        vec.push_back(info);


    }
}

bool DbManager::selectUserName(QString name)
{
    QString sql = "select count(*) from user where userName = ?;";
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,name);
    query.exec();
    int count = -1;
    if(query.next()) {;
        count = query.record().value(0).toInt();
        if(count>0)
        {
            return true;
        }
        else
            return false;
    }
    return true;
}

bool DbManager::insertUser(QString userName, QString passWord)
{
    QString sql = "insert into user(userName,passWord) values(?,?)";
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,userName);
    query.bindValue(1,passWord);
    bool ok = query.exec();
    if(ok)
        qDebug()<<"插入成功";
    else
        qDebug()<<"插入失败";
    return ok;
}

bool DbManager::userLogin(QString name, QString pass)
{
    QString sql = "select count(*) from user where userName = ? and passWord  = ?";

    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(0,name);
    query.bindValue(1,pass);
    query.exec();

    int count = -1;

    //取查询之后 的结果（满足条件的数量有几个整数 ）
    while (query.next()) {
        count = query.record().value(0).toInt();
        qDebug()<<"count:"<<count;
        if(count > 0)
            return true;
        else
            return false;
    }
    return  false;
}

DbManager::~DbManager()
{
    db.close();
}
